<?php
class  Employer_Model_DbTable_Cv  extends  Model_DbTable_Common
{
    protected $_name = 'cv' ;
	
	public function searchResume($formData, $command){
		try {
			$params = array();
			if($command == 'exactly'){
				$sql = 'SELECT a.*, d.name as location ';
				if(!empty($formData['salaryfrom']) || !empty($formData['salaryto']))
					$sql .= ', CONVERT(SUBSTRING_INDEX(a.salary,"-",-1),UNSIGNED INTEGER) as salary ';
				$sql .= 'FROM `cv` a
						INNER JOIN `cv_detail` b
							ON a.id = b.cv_id
						INNER JOIN `cv_province` c
							ON a.id = c.cv_id
						INNER JOIN `province` d
							ON c.province_id = d.id
						WHERE 	( a.`expected_position` like ?
						OR		b.`workarchive` like ?
						OR		b.`educationarchive` like ?
						OR		b.`skill` like ?)
						';
				$params[] = $params[] = $params[] = $params[] = "%".$formData['keywords']."%";
				
			}
			else{
				$sql = 'SELECT a.job_id, b.title, c.companyname, b.expire_date, d.name as salary, e.name, a.applied_date, a.create_date, a.applied as applyStatus
						FROM `saved_job` a  
						INNER JOIN `job` b
							ON a.job_id = b.id
						INNER JOIN `employer` c
							ON b.employer_id = c.id
						INNER JOIN `salary_range` d
							ON b.salary_range_id = d.id
						INNER JOIN `province` e
							ON b.province_id = e.id
						WHERE employee_id = ?';
			}
			
			//Gender
				if($formData['gender'] != 'any'){
					$sql .= ' AND gender = ?';
					$params[] = $formData['gender'];
				}
			//experience
				if(!empty($formData['experience'])){
					$sql .= ' AND experience = ?';
					$params[] = $formData['experience'];
				}
			//Location
				if(!empty($formData['location'])){
					$sql .= ' AND d.id = ? ';
					$params[] = $formData['location'];
				}
			//Resume update
				if(!empty($formData['salary-update-range'])){
					
					$sql .= ' AND d.id = ? ';
					$params[] = $formData['location'];
				}
				
			//Salary
				if(!empty($formData['salaryfrom'])){
					$sql .= ' AND CONVERT(SUBSTRING_INDEX(a.salary,"-",-1),UNSIGNED INTEGER) >= ?';
					$params[] = $formData['salaryfrom'];
				}
				if(!empty($formData['salaryto'])){
					$sql .= ' AND CONVERT(SUBSTRING_INDEX(a.salary,"-",-1),UNSIGNED INTEGER) <= ?';
					$params[] = $formData['salaryto'];
				}
			
			echo "sql la :".$sql;
			Zend_Debug::dump($params);
			
			
			$this->_db->setFetchMode(Zend_Db::FETCH_ASSOC);
			$result = $this->_db->fetchAll ( $sql, $params );
			Zend_Debug::dump($result);
			die;		
			return $result;
		} catch ( Exception $ex ) {
			echo "<br/> Can not get resume, <br/>Error: " . $ex->getMessage ();
			exit ();
		}
	}
}
